<?php
//**************************************************
// all query string should be quoted by double quote
//**************************************************

/*
	return db resource or false
*/
function connGet()
{
	global $DBADD, $DBUSER, $DBPASS, $DBNAME;
	
	$conn = mysql_pconnect($DBADD, $DBUSER, $DBPASS);
	if ($conn == false)
	{
		return false;
	}
	
	if (mysql_select_db($DBNAME, $conn) == false)
	{
		return false;
	}
	
	return $conn;
}

function connClose($conn)
{
	mysql_close($conn);
}

/*
	userinfo contains all user related information (hash map)
	return: false, user id, dup error
*/
function addUser($userinfo)
{
	global $EMAILDUP_ERROR, $USERINFO_TB_NAME;
	
	$result = array();
	$conn = connGet();
	if ($conn == false)
	{
		return false;
	}
	
	// check duplicated email
	$query = "select * from ".$USERINFO_TB_NAME." where company_email = ".procStrField($userinfo['company_email']);		
	$resource = mysql_query($query,$conn);
	if ($resource == false)
	{
		connClose($conn);
		return false;
	}
	else 
	{
		if (mysql_num_rows($resource) == 1)
		{
			return $EMAILDUP_ERROR;
		}
	}		
	
	$query = "insert into ".$USERINFO_TB_NAME." (contact_name,company_name,hst_number,company_address,company_city,company_province,company_postal_code,company_phone_number,company_fax_number,company_email,Password) values(".
		procStrField($userinfo['contact_name']).",".
		procStrField($userinfo['company_name']).",".
		procStrField($userinfo['hst_number']).",".
		procStrField($userinfo['company_address']).",".
		procStrField($userinfo['company_city']).",".
		procStrField($userinfo['company_province']).",".
		procStrField($userinfo['company_postal_code']).",".
		procStrField($userinfo['company_phone_number']).",".
		procStrField($userinfo['company_fax_number']).",".
		procStrField($userinfo['company_email']).",".
		procStrField($userinfo['password']).")";
	
	$resource = mysql_query($query,$conn);

	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$query = "select LAST_INSERT_ID(ID) as ID from ".$USERINFO_TB_NAME." order by ID desc limit 1";		
		$resource = mysql_query($query,$conn);
		if ($resource == false){
			connClose($conn);
			return false;
		}
		else {
			$result = mysql_fetch_assoc($resource);
			connClose($conn);
			// return user id
			return $result['ID'];
		}
	}
}

/*
	return false or user hash
*/
function getUserById($id){
	global $USERINFO_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$USERINFO_TB_NAME." where ID = ".$id;
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}


function getPasswdByEmail($email){
	global $EMAILDUP_ERROR,$USERINFO_TB_NAME,$PASSWORD_NOT_FOUND;

	$result = array();
	$conn = connGet();
	if ($conn == false)
	{
		return false;
	}
	
	// get password
	$query = "select * from ".$USERINFO_TB_NAME." where company_email = '".$email."'";
	print('}}}'.$query);
	$resource = mysql_query($query,$conn);
	if ($resource == false)
	{
		connClose($conn);
		return false;
	}
	else 
	{
		if (mysql_num_rows($resource) > 1 || mysql_num_rows($resource) < 0)
		{
			return $EMAILDUP_ERROR;
		}
		else {
			$row = mysql_fetch_assoc($resource);
			connClose($conn);
			if ($row['password'] != ''){
				return $row['password'];
			}
			else {
				print('>>>'.$row['password']);
				return $PASSWORD_NOT_FOUND;
			}
		}
	}		
}
/*
	return false or user hash
*/
function getUserByEmail($email){
	global $USERINFO_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$USERINFO_TB_NAME." where company_email = ".procStrField($email);
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}

/*
	userinfo contains all user related information (hash map)
	return: false, true
*/
function updateUserById($id, $userinfo){
	global $USERINFO_TB_NAME;
	
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "update ".$USERINFO_TB_NAME." set ".
		"ContactName =".procStrField($userinfo['ContactName']).",".
		"DoctorName =".procStrField($userinfo['DoctorName']).",".
		"CompanyName =".procStrField($userinfo['CompanyName']).",".
		"FullAddress =".procStrField($userinfo['FullAddress']).",".
		"City =".procStrField($userinfo['City']).",".
		"Province =".procStrField($userinfo['Province']).",".
		"PostalCode =".procStrField($userinfo['PostalCode']).",".
		//"PhoneNumber =".procStrField($userinfo['PhoneNumber']).",".
		"FaxNumber =".procStrField($userinfo['FaxNumber']).",".
		"Email =".procStrField($userinfo['Email']).",".
		"MethodPayment =".procStrField($userinfo['MethodPayment']).",".
		"Password =".procStrField($userinfo['Password']).",".
		"Block =".$userinfo['Block'].
		" where ID = ".$id;

	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}
}


/*
  	retrieving data from 
*/
function getProductListByProductID($pid){
	global $PRODUCTS_ITEMS;

	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_ITEMS." where ProductID = ".$pid;
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}


/*--------------------------------------------
	Category Data Access
	Return false or categories hash
----------------------------------------------*/
// Get menu items

function getMenuItems()
{
	$catArray = getCategories();
	for ($n = 0; $n < count($catArray); $n++)
	{
		$arr = $catArray[$n];
		$subArr = array();
		
		$subArr = getSubCategoriesByCategoryID($arr['id']);		
		array_push($catArray[$n], $subArr);

	}
	return $catArray;
}

function getMenuHtml()
{
	$menuSubArr =  getMenuItems();
	//showArr($subArr);
	
	$menu_out = '';
	for ($n = 0; $n < count($menuSubArr); $n++)
	{
		$menuArr = $menuSubArr[$n];	
		$menu_out .= '<h3 class="headerbar"><a href="parts.php?cid='.$menuArr['id'].'">'.$menuArr['category_name'].'</a></h3>';
		$menu_out .= '<ul class="submenu">';
		
		$menuMidArr = $menuArr['0'];
		for ($k = 0; $k < count($menuMidArr); $k++)
		{
			$menuFinArr = $menuMidArr[$k];
			$menu_out .= '<li><a href="parts2.php?scid='.$menuFinArr['id'].'" >'.$menuFinArr['sub_category_name'].'</a></li>';
		}
		$menu_out .= '</ul>';
	}
	
	return $menu_out;
}

function getMenuHtmlClearance()
{
	$menuSubArr =  getMenuItems();
	//showArr($subArr);
	
	$menu_out = '';
	for ($n = 0; $n < count($menuSubArr); $n++)
	{
		$menuArr = $menuSubArr[$n];	
		$menu_out .= '<h3 class="headerbar"><a href="clearance.php?cid='.$menuArr['id'].'">'.$menuArr['category_name'].'</a></h3>';
		$menu_out .= '<ul class="submenu">';
		
		$menuMidArr = $menuArr['0'];
		for ($k = 0; $k < count($menuMidArr); $k++)
		{
			$menuFinArr = $menuMidArr[$k];
			$menu_out .= '<li><a href="clearance2.php?scid='.$menuFinArr['id'].'" >'.$menuFinArr['sub_category_name'].'</a></li>';
		}
		$menu_out .= '</ul>';
	}
	
	return $menu_out;
}

/*
	return false or categories list hash
*/
function getCategoryByID($cid)
{
	global $CATEGORIES_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$CATEGORIES_TB_NAME." where id = ".$cid;
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}

function getCategories(){
	global $CATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$CATEGORIES_TB_NAME;

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

function updateCategoryActiveByID($id, $active){
	global $CATEGORIES_TB_NAME;
	
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "update ".$CATEGORIES_TB_NAME." set active = ".$active." where id = ".$id;
	$resource = mysql_query($query, $conn);
	
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}	
	
}

function addCategory($categoryInfo){
	global $CATEGORIES_TB_NAME;
	
	$result = array();
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	// check duplicated email
	$query = "select * from ".$CATEGORIES_TB_NAME." where category_name = ".procStrField($categoryInfo['category_name']);		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		if (mysql_num_rows($resource) == 1){
			return $CATEGORY_NAME_DUP_ERROR;
		}
	}		
	
	$query = "insert into ".$CATEGORIES_TB_NAME." (cateogry_name,category_img,category_description,brand_id,active,created_date,updated_date) values(".
		procStrField($categoryInfo['category_name']).",".
		procStrField($categoryInfo['category_img']).",".
		procStrField($categoryInfo['category_description']).",".
		procStrField($categoryInfo['brand_id']).",".
		procStrField($categoryInfo['active']).",".
		procStrField($categoryInfo['created_date']).",".
		procStrField($categoryInfo['updated_date']).")";
	
	$resource = mysql_query($query,$conn);

	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$query = "select LAST_INSERT_ID(ID) as ID from ".$CATEGORIES_TB_NAME." order by ID desc limit 1";		
		$resource = mysql_query($query,$conn);
		if ($resource == false){
			connClose($conn);
			return false;
		}
		else {
			$result = mysql_fetch_assoc($resource);
			connClose($conn);
			// return user id
			return $result['ID'];
		}
	}
}


/*--------------------------------------------
	Brand Data Access
	return false or brands hash
--------------------------------------------*/
function getBrands(){
	global $BRANDS_TB_NAME;

	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$BRANDS_TB_NAME;
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

/*
	Get brand for navigator
*/
function getBrandForNavigator($bid)
{
	global $BRANDS_TB_NAME;
	global $CATEGORIES_TB_NAME;
	global $SUBCATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select bra.brand_name, bra.brand_description, ";
	$query .= "subcat.sub_category_name, cat.category_name from ";
	$query .= $BRANDS_TB_NAME;
	$query .= " as bra inner join ".$SUBCATEGORIES_TB_NAME." as subcat on bra.sub_category_id = subcat.id";
	$query .= " inner join ".$CATEGORIES_TB_NAME." as cat on subcat.category_id = cat.id";
	$query .= " where bra.id = ".$bid." and bra.active = true and subcat.active = true and cat.active = true";
	//echo $query;
	//exit;

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

/*
	Get brands list for update
*/
function getBrandsAll(){
	global $BRANDS_TB_NAME;
	global $CATEGORIES_TB_NAME;
	global $SUBCATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select bra.id, bra.brand_name, bra.brand_img, bra.brand_description, bra.sub_category_id, bra.active,";
	$query .= "subcat.sub_category_name, subcat.category_id, cat.category_name from ";
	$query .= $BRANDS_TB_NAME;
	$query .= " as bra inner join ".$SUBCATEGORIES_TB_NAME." as subcat on bra.sub_category_id = subcat.id";
	$query .= " inner join ".$CATEGORIES_TB_NAME." as cat on subcat.category_id = cat.id";
	$query .= " where bra.active = true and subcat.active = true and cat.active = true";
	//echo $query;
	//exit;

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

function getBrandsBySubCategoryID($subID){
	global $BRANDS_TB_NAME;

	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$BRANDS_TB_NAME." where sub_category_id = ".$subID;
	
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

function getBrandsByID($bid){
	global $BRANDS_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$BRANDS_TB_NAME." where id = ".$bid;
	
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}

/*----Insert section----*/
function popBrandInfo($POST){
	$binfo = array();
	$binfo['id'] = $POST['brand_id'];
	$binfo['brand_name'] = $POST['brand_name'];
	$binfo['brand_img_input'] = '';
	$binfo['brand_description'] = $POST['brand_description'];
	$binfo['brand_sub_category_id'] = $POST['brand_sub_category_id'];
	if ($POST['chk_brand_active'] == 'on'){
		$binfo['chk_brand_active'] = '1';
	}
	else {
		$binfo['chk_brand_active'] = '0';
	}
	
	return $binfo;
}

function insertBrand($binfo){
	global $BRANDS_TB_NAME;
	$query = 'insert into '.$BRANDS_TB_NAME.'  (brand_name,brand_img,brand_description,sub_category_id,active,created_date,updated_date) values('.
			'"'.addslashes($binfo['brand_name']).'",'.
			'"'.addslashes($binfo['brand_img_input']).'",'.
			'"'.addslashes($binfo['brand_description']).'",'.
			'"'.addslashes($binfo['brand_sub_category_id']).'",'.
			'"'.addslashes($binfo['chk_brand_active']).'",'.
			'null,'.
			'null'.')';
	//print($query);
	$conn = connGet();
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		return true;
	}		
}

/*----update section----*/

function updateBrand($binfo)
{
	global $BRANDS_TB_NAME;
	
	if ($binfo['brand_img_input'] != '')
	{
		
		$query = 'update '.$BRANDS_TB_NAME.' set '.
			'brand_name = '.'"'.addslashes($binfo['brand_name']).'"'.','.
			'brand_img = '.'"'.addslashes($binfo['brand_img_input']).'"'.','.
			'brand_description = '.'"'.addslashes($binfo['brand_description']).'"'.','.
			'sub_category_id = '.'"'.addslashes($binfo['brand_sub_category_id']).'"'.','.
			'active = '.'"'.addslashes($binfo['chk_brand_active']).'"'.' where id = ' .$binfo['id'];
	}
	else
	{			
		$query = 'update '.$BRANDS_TB_NAME.' set '.
			'brand_name = '.'"'.addslashes($binfo['brand_name']).'"'.','.			
			'brand_description = '.'"'.addslashes($binfo['brand_description']).'"'.','.
			'sub_category_id = '.'"'.addslashes($binfo['brand_sub_category_id']).'"'.','.
			'active = '.'"'.addslashes($binfo['chk_brand_active']).'"'.' where id = ' .$binfo['id'];

	}	
	
	//echo $query;
	//exit;
	
	$conn = connGet();
	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}
	

}

function deleteBrandByID($id)
{
	global $BRANDS_TB_NAME;
	$query = 'delete from '.$BRANDS_TB_NAME.' where id = '.$id;
	$conn = connGet();
	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}	
}


/*--------------------------------------------
	Product Data Access
	Return false or products hash
--------------------------------------------*/
function getProductsByCategoryID($categoryID){
	global $PRODUCTS_TB_NAME;

	$result = array();
	$output = array();	
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_TB_NAME." where category_id = ".$categoryID." and active = true and is_clearance != 1";
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

function getProductsByBrandID($bid){
	global $PRODUCTS_TB_NAME;

	$result = array();
	$output = array();	
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_TB_NAME." where brand_id = ".$bid." and active = true and is_clearance != 1";
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

// get products by brand id in clearance
function getProductsByBrandIDClearance($bid){
	global $PRODUCTS_TB_NAME;

	$result = array();
	$output = array();	
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_TB_NAME." where brand_id = ".$bid." and active = true and is_clearance = 1";
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}



// get products in clearance order by id
function getProductsClearance(){
	global $PRODUCTS_TB_NAME;

	$result = array();
	$output = array();	
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_TB_NAME." where active = true and is_clearance = 1 order by id";
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

// get products in home page order by id
function getProductsHomePage(){
	global $PRODUCTS_TB_NAME;

	$result = array();
	$output = array();	
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_TB_NAME." where active = true and is_home_page = 1 order by id limit 0,4";
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}


/*
	return false or products hash
*/
function getProductsByProductID($pid){
	global $PRODUCTS_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_TB_NAME." where id = ".$pid." and active = true";
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}

/*
	return false or products in clearance
*/
function getProductsByProductIDClearance($pid){
	global $PRODUCTS_TB_NAME;

	$result = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$PRODUCTS_TB_NAME." where id = ".$pid." and active = true and is_clearance = 1";
		
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}


/*--------------------------------------------	
	get product related information for 
	navigator
----------------------------------------------*/
function getProductForNavigator($pid)
{
	global $PRODUCTS_TB_NAME;
	global $BRANDS_TB_NAME;
	global $CATEGORIES_TB_NAME;
	global $SUBCATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select pro.product_name, pro.product_description, ";
	$query .= "bra.brand_name, subcat.sub_category_name, cat.category_name from ";
	$query .= $PRODUCTS_TB_NAME." as pro inner join ".$BRANDS_TB_NAME;
	$query .= " as bra on pro.brand_id = bra.id ";
	$query .= " inner join ".$SUBCATEGORIES_TB_NAME." as subcat on bra.sub_category_id = subcat.id";
	$query .= " inner join ".$CATEGORIES_TB_NAME." as cat on subcat.category_id = cat.id";
	$query .= " where pro.id = ".$pid." and pro.active = true and bra.active = true and subcat.active = true and cat.active = true";
	//echo $query;
	//exit;

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}



function getProductAll()
{
	global $PRODUCTS_TB_NAME;
	global $BRANDS_TB_NAME;
	global $CATEGORIES_TB_NAME;
	global $SUBCATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	//$query = "select t1.id, t1.category_name, t1.category_img, t1.category_description, t1.active, t2.brand_name from ";
	//$query .= $CATEGORIES_TB_NAME." as t1 inner join ".$BRANDS_TB_NAME;
	//$query .= " as t2 on t1.brand_id = t2.id where t1.active = true and t2.active = true";

	$query = "select pro.id, pro.product_name, pro.product_img, pro.product_description, pro.is_home_page, pro.is_clearance,";
	$query .= "pro.brand_id, pro.product_price, pro.product_discount_price, pro.active, bra.sub_category_id, ";
	$query .= "bra.brand_name, subcat.sub_category_name, subcat.category_id, cat.category_name from ";
	$query .= $PRODUCTS_TB_NAME." as pro inner join ".$BRANDS_TB_NAME;
	$query .= " as bra on pro.brand_id = bra.id ";
	$query .= " inner join ".$SUBCATEGORIES_TB_NAME." as subcat on bra.sub_category_id = subcat.id";
	$query .= " inner join ".$CATEGORIES_TB_NAME." as cat on subcat.category_id = cat.id";
	$query .= " where pro.active = true and bra.active = true and subcat.active = true and cat.active = true and pro.is_clearance != 1";
	//echo $query;
	//exit;

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

/*----Insert section----*/
function popProdInfo($POST){
	$prodInfo = array();
	$prodInfo['id'] = $POST['product_id'];
	//$prodInfo['product_category_select'] = $POST['product_category_select'];
	//$prodInfo['product_sub_category_select'] = $POST['product_sub_category_select'];
	$prodInfo['product_brand'] = $POST['product_brand'];
	$prodInfo['product_name'] = $POST['product_name'];
	$prodInfo['product_img_input'] = '';
	$prodInfo['product_description'] = $POST['product_description'];
	$prodInfo['product_price'] = $POST['product_price'];
	$prodInfo['product_discount_price'] = $POST['product_discount_price'];
	if ($POST['chk_product_active'] == 'on'){
		$prodInfo['chk_product_active'] = '1';
	}
	else {
		$prodInfo['chk_product_active'] = '0';
	}
	
	if ($POST['chk_product_is_home_page'] == 'on'){
		$prodInfo['chk_product_is_home_page'] = '1';
	}
	else {
		$prodInfo['chk_product_is_home_page'] = '0';
	}
	
	if ($POST['chk_product_is_clearance'] == 'on'){
		$prodInfo['chk_product_is_clearance'] = '1';
	}
	else {
		$prodInfo['chk_product_is_clearance'] = '0';
	}
	
	return $prodInfo;
}

function insertProd($prodInfo){
	global $PRODUCTS_TB_NAME;
	$query = 'insert into '.$PRODUCTS_TB_NAME.'  (product_name,product_img,product_description,brand_id,product_price,product_discount_price,active,is_home_page,is_clearance,created_date,updated_date) values('.
			'"'.addslashes($prodInfo['product_name']).'",'.
			'"'.addslashes($prodInfo['product_img_input']).'",'.
			'"'.addslashes($prodInfo['product_description']).'",'.
			'"'.addslashes($prodInfo['product_brand']).'",'.
			'"'.addslashes($prodInfo['product_price']).'",'.
			'"'.addslashes($prodInfo['product_discount_price']).'",'.
			'"'.addslashes($prodInfo['chk_product_active']).'",'.
			'"'.addslashes($prodInfo['chk_product_is_home_page']).'",'.
			'"'.addslashes($prodInfo['chk_product_is_clearance']).'",'.						
			'null,'.
			'null'.')';
	//print($query);
	$conn = connGet();
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		return true;
	}		
}

/*----update section----*/

function updateProd($prodInfo)
{
	global $PRODUCTS_TB_NAME;
	
	if ($prodInfo['product_img_input'] != '')
	{
		
		$query = 'update '.$PRODUCTS_TB_NAME.' set '.
			'product_name = '.'"'.addslashes($prodInfo['product_name']).'"'.','.
			'product_img = '.'"'.addslashes($prodInfo['product_img_input']).'"'.','.
			'product_description = '.'"'.addslashes($prodInfo['product_description']).'"'.','.
			'brand_id = '.'"'.addslashes($prodInfo['product_brand']).'"'.','.
			'product_price = '.'"'.addslashes($prodInfo['product_price']).'"'.','.
			'product_discount_price = '.'"'.addslashes($prodInfo['product_discount_price']).'"'.','.
			'is_home_page = '.'"'.addslashes($prodInfo['chk_product_is_home_page']).'"'.','.
			'is_clearance = '.'"'.addslashes($prodInfo['chk_product_is_clearance']).'"'.','.						
			'active = '.'"'.addslashes($prodInfo['chk_product_active']).'"'.' where id = ' .$prodInfo['id'];
	}
	else
	{			
		$query = 'update '.$PRODUCTS_TB_NAME.' set '.
			'product_name = '.'"'.addslashes($prodInfo['product_name']).'"'.','.			
			'product_description = '.'"'.addslashes($prodInfo['product_description']).'"'.','.
			'brand_id = '.'"'.addslashes($prodInfo['product_brand']).'"'.','.
			'product_price = '.'"'.addslashes($prodInfo['product_price']).'"'.','.
			'product_discount_price = '.'"'.addslashes($prodInfo['product_discount_price']).'"'.','.
			'is_home_page = '.'"'.addslashes($prodInfo['chk_product_is_home_page']).'"'.','.
			'is_clearance = '.'"'.addslashes($prodInfo['chk_product_is_clearance']).'"'.','.			
			'active = '.'"'.addslashes($prodInfo['chk_product_active']).'"'.' where id = ' .$prodInfo['id'];

	}	
	
	//echo $query;
	//exit;
	
	$conn = connGet();
	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}
	

}

function deleteProdByID($id)
{
	global $PRODUCTS_TB_NAME;
	$query = 'delete from '.$PRODUCTS_TB_NAME.' where id = '.$id;
	$conn = connGet();
	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}	
}


/*--------------------------------------------
	sub_category Data Access
	Return false or categories hash
----------------------------------------------*/
function getSubCategoriesByCategoryID($categoryID){
	global $SUBCATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}

	$query = "select * from ".$SUBCATEGORIES_TB_NAME." where category_id = ".$categoryID;

	//$query = "select t1.id, t1.category_name, t1.category_img, t1.category_description, t1.active, t2.brand_name from ";
	//$query .= $CATEGORIES_TB_NAME." as t1 inner join ".$BRANDS_TB_NAME;
	//$query .= " as t2 on t1.brand_id = t2.id where t1.active = true and t2.active = true";

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

/*--------------------------------------------
	sub_category Data Access
	Return false or categories hash
----------------------------------------------*/
function getSubCategoriesByID($scid)
{
	global $SUBCATEGORIES_TB_NAME;
	global $CATEGORIES_TB_NAME;
	
	$result = array();
		
	$conn = connGet();
	if ($conn == false)
	{
		return false;
	}

	$query = "select * from ".$SUBCATEGORIES_TB_NAME." where id = ".$scid;		
	$resource = mysql_query($query,$conn);
	if ($resource == false)
	{
		connClose($conn);
		return false;
	}
	else 
	{
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}


/*--------------------------------------------
	sub_category Data Access
	get sub category related information for 
	navigator
----------------------------------------------*/
function getSubCategoriesForNavigator($scid)
{
	global $SUBCATEGORIES_TB_NAME;
	global $CATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false)
	{
		return false;
	}

	$query = "select subcat.sub_category_name, subcat.sub_category_description,cat.category_name ";
	$query .= "from ".$SUBCATEGORIES_TB_NAME." as subcat inner join ".$CATEGORIES_TB_NAME." as cat on ";
	$query .= "subcat.category_id = cat.id where subcat.id = ".$scid." and cat.active = true and subcat.active = true";		
	
	//echo $query;
	//exit;
	
	$resource = mysql_query($query,$conn);
	if ($resource == false)
	{
		connClose($conn);
		return false;
	}
	else 
	{
		$result = mysql_fetch_assoc($resource);
		connClose($conn);
		return $result;
	}
}


function getSubCategories(){
	global $SUBCATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}
	
	$query = "select * from ".$SUBCATEGORIES_TB_NAME;
	//$query = "select t1.id, t1.category_name, t1.category_img, t1.category_description, t1.active, t2.brand_name from ";
	//$query .= $CATEGORIES_TB_NAME." as t1 inner join ".$BRANDS_TB_NAME;
	//$query .= " as t2 on t1.brand_id = t2.id where t1.active = true and t2.active = true";

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

function popSubCategoryInfo($POST){
	$sinfo = array();
	$sinfo['id'] = $POST['id'];	
	$sinfo['category_id'] = $POST['category_id'];
	$sinfo['sub_category_name'] = $POST['sub_category_name'];
	$sinfo['sub_category_img_input'] = '';
	$sinfo['sub_category_description'] = $POST['sub_category_description'];
	if ($POST['chk_sub_category_active'] == 'on'){
		$sinfo['active'] = '1';
	}
	else {
		$sinfo['active'] = '0';
	}
	
	return $sinfo;
}

function insertSubCategory($sc){
	global $SUBCATEGORIES_TB_NAME;
	
	$query = 'insert into '.$SUBCATEGORIES_TB_NAME.'  (sub_category_name,sub_category_img,sub_category_description,category_id,active,created_date,updated_date) values('.
			'"'.addslashes($sc['sub_category_name']).'",'.
			'"'.addslashes($sc['sub_category_img_input']).'",'.
			'"'.addslashes($sc['sub_category_description']).'",'.
			'"'.addslashes($sc['category_id']).'",'.
			'"'.addslashes($sc['active']).'",'.
			'null,'.
			'null'.')';
	//print($query);
	$conn = connGet();
	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		return true;
	}		
}

/*----update section----*/

function updateSubCategory($sc)
{
	global $SUBCATEGORIES_TB_NAME;
	
	if ($sc['sub_category_img_input'] != '')
	{
		
		$query = 'update '.$SUBCATEGORIES_TB_NAME.' set '.
		'sub_category_name = '.'"'.addslashes($sc['sub_category_name']).'"'.','.
		'sub_category_img = '.'"'.addslashes($sc['sub_category_img_input']).'"'.','.
		'sub_category_description = '.'"'.addslashes($sc['sub_category_description']).'"'.','.
		'category_id = '.'"'.addslashes($sc['category_id']).'"'.','.
		'active = '.'"'.addslashes($sc['active']).'"'.' where id = ' .$sc['id'];
	}
	else
	{			
		$query = 'update '.$SUBCATEGORIES_TB_NAME.' set '.
			'sub_category_name = '.'"'.addslashes($sc['sub_category_name']).'"'.','.			
			'sub_category_description = '.'"'.addslashes($sc['sub_category_description']).'"'.','.
			'category_id = '.'"'.addslashes($sc['category_id']).'"'.','.
			'active = '.'"'.addslashes($sc['active']).'"'.' where id = ' .$sc['id'];

	}	
	
	//echo $query;
	//exit;
	
	$conn = connGet();
	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}
}

function getSubCategoryAll()
{
	global $CATEGORIES_TB_NAME;
	global $SUBCATEGORIES_TB_NAME;
	
	$result = array();
	$output = array();
		
	$conn = connGet();
	if ($conn == false){
		return false;
	}

	$query = "select subcat.id, subcat.sub_category_name, subcat.sub_category_img, subcat.sub_category_description, ";
	$query .=" subcat.category_id, subcat.active, cat.category_name ";
	$query .= "from ";
	$query .= $SUBCATEGORIES_TB_NAME." as subcat inner join ".$CATEGORIES_TB_NAME;
	$query .= " as cat on subcat.category_id = cat.id";
	$query .= " where subcat.active = true and cat.active = true";
	//echo $query;
	//exit;

	$resource = mysql_query($query,$conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		while ($result = mysql_fetch_assoc($resource))
		{
			array_push($output, $result);
		}
		connClose($conn);
		return $output;
	}
}

function deleteSubCategoryByID($id)
{
	global $SUBCATEGORIES_TB_NAME;
	$query = 'delete from '.$SUBCATEGORIES_TB_NAME.' where id = '.$id;
	$conn = connGet();
	$resource = mysql_query($query, $conn);
	if ($resource == false){
		connClose($conn);
		return false;
	}
	else {
		connClose($conn);
		return true;
	}	
}


?>